package com.qy127.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * @ClassName JDBCUtil
 * @Description ToDo
 * @Auther teacher Bai
 * @Date 2020/12/3 9:57
 */
public class JDBCUtil {
    private static DataSource dataSource = null;
    static {
        try {
            //读取properties配置文件的参数
            Properties p = new Properties();
            InputStream stream = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            p.load(stream);
            //使用工厂模式
            dataSource = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //方法的三要素：返回值，方法名，参数列表
    /**
     * 封装获取连接的方法
     * @return
     */
    public static Connection getConn(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    /**
     *封装List<List>数据类型</>
     * @param sql
     * @param obj
     * @return
     */
    public static List<List> queryList(String sql,Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet set = null;
        List<List> bigList = null;
        //3.预编译
        try {
            ps = conn.prepareStatement(sql);
            //向sql传参
            setParmeter(ps,obj);
            //执行sql
            set = ps.executeQuery();
            bigList = new ArrayList<>();
            //遍历结果集
            while (set.next()){
                List<Object> list = new ArrayList<>();
                for (int i = 0; i < set.getMetaData().getColumnCount(); i++) {
                    list.add(set.getObject(i+1));
                }
                bigList.add(list);
            }
            //return bigList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return bigList;
    }
    /**
     *封装List<Map<String,Object>类型的数据</>></>
     * @param sql
     * @param obj
     * @return
     */
    public static List<Map<String,Object>> queryListMap(String sql, Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet set = null;
        List<Map<String,Object>> bigList = null;
        //3.预编译
        try {
            ps = conn.prepareStatement(sql);
            //向sql传参
            setParmeter(ps,obj);
            //执行sql
            set = ps.executeQuery();
            bigList = new ArrayList<>();
            //遍历结果集
            while (set.next()){
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < set.getMetaData().getColumnCount(); i++) {
                    map.put(set.getMetaData().getColumnName(i+1),set.getObject(i+1));
                }
                bigList.add(map);
            }
            //return bigList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return bigList;
    }
    /**
     * 封装关闭方法
     * @param set
     * @param ps
     * @param conn
     */
    public static void closeAll(ResultSet set, PreparedStatement ps, Connection conn){
        //因为如果为null,会报NullPointerException
        try {
            if(set!=null){
                set.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(ps!=null){
                ps.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    /**
     * 封装设置参数的方法
     * @param ps
     * @param obj
     */
    public static void setParmeter(PreparedStatement ps,Object... obj){
        if(obj.length>0){
            for (int i = 0; i < obj.length; i++) {
                try {
                    ps.setObject(i+1,obj[i]);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
    /**
     * 通用增删改方法的封装
     * @param sql
     * @param obj
     * @return
     */
    public static int update(String sql, Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        //3.预编译
        try {
            ps = conn.prepareStatement(sql);
            //向sql传参
            setParmeter(ps,obj);
            //执行sql
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(null,ps,conn);
        }
        return -1;
    }
    /**
     * 封装返回主键的方法
     * @param sql
     * @param obj
     * @return
     */
    public static int getPrimaryKey(String sql, Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        ResultSet keys = null;
        //3.预编译
        try {
            ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            //向sql传参
            setParmeter(ps,obj);
            //执行sql
            ps.executeUpdate();
            //获取主键的方法
            keys = ps.getGeneratedKeys();
            if(keys.next()){
                return keys.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(keys,ps,conn);
        }
        return -1;
    }
}
